const { sqlConnect } = require('../../plugin/db');
const db = require('../../plugin/db');
const utils = require('../../plugin/utils');
const classify = {
    /**添加修改分类 */
    addClassify (req, res) {
        const data = req.body;
        const type_id = data.type_id;
        const rank = data.rank;//添加分类的第几级
        const edit_id = data.edit_id;//如果不为NULL就是修改分类
        const name = data.name.trim();//分类名称
        const classify_one_id = data.classify_one_id;
        const classify_two_id = data.classify_two_id;
        const create_time = new Date();//当前系统时间
        if (!name) {
            utils.error(res, "分类名称不能为空");
            return;
        }
        if (classify_one_id == '') {
            utils.error(res, "请选择一级分类");
            return;
        }
        if (classify_two_id == '') {
            utils.error(res, "请选择二级分类");
            return;
        }
        //公共调用
        const pluginClassify = (tableName, tableTd, tableVal) => {
            if (edit_id) {//edit_id 为null是添加分类否则是修改分类
                editClassify(tableName, tableTd, tableVal);
            }
            else {//添加分类
                addClassify(tableName, tableTd, tableVal)
            }
        }
        //添加分类封装的方法
        const addClassify = (tableName, tableTd, tableVal) => {
            let sql = null;
            //查询数据库分类是否已存在
            switch (rank) {
                case 1:
                    sql = `select name from ${tableName} where name='${name}' and type_id=${type_id} limit 0,1`;
                    break;
                case 2:
                    sql = `select name from ${tableName} where name='${name}' and classify_one_id=${classify_one_id} and type_id=${type_id} limit 0,1`;
                    break;
                case 3:
                    sql = `select name from ${tableName} where name='${name}' and classify_one_id=${classify_one_id} and classify_two_id=${classify_two_id} and type_id=${type_id} limit 0,1`;
                    break;
            }
            const sqlArr = [];
            const callBack = (err, result) => {
                if (err) {
                    utils.error(res, '数据库出错');
                    return;
                }
                if (result.length) {
                    utils.error(res, "添加分类已存在")
                    return;
                }
                //添加进数据库
                let tableValNum = '';
                for (let i = 0; i < tableVal.length; i++) {
                    tableValNum += '?,'
                }
                tableValNum = tableValNum.slice(0, tableValNum.length - 1);
                const sql = `insert into ${tableName} (${tableTd}) value (${tableValNum})`;
                const sqlArr = tableVal
                const callBack = (err, result) => {
                    if (err) {
                        utils.error(res, '添加分类失败');
                        return;
                    }
                    utils.success(res, '添加分类成功')
                }
                db.sqlConnect(sql, sqlArr, callBack);
            }
            db.sqlConnect(sql, sqlArr, callBack);
        }
        //修改分类封装的方法
        const editClassify = (tableName, tableTd, tableVal) => {
            let sql = null;
            //查询数据库分类是否已存在
            switch (rank) {
                case 1:
                    sql = `select name from ${tableName} where name='${name}' and type_id=${type_id} limit 0,1`;
                    break;
                case 2:
                    sql = `select name from ${tableName} where name='${name}' and classify_one_id=${classify_one_id} and type_id=${type_id} limit 0,1`;
                    break;
                case 3:
                    sql = `select name from ${tableName} where name='${name}' and classify_one_id=${classify_one_id} and classify_two_id=${classify_two_id} and type_id=${type_id} limit 0,1`;
                    break;
            }
            const sqlArr = [];
            const callBack = (err, result) => {
                if (err) {
                    utils.error(res, '数据库出错');
                    return;
                }
                if (result.length) {
                    utils.error(res, "添加分类已存在")
                    return;
                }

                switch (rank) {
                    case 2:
                        const transaction = [{
                            sql: `UPDATE ${tableName} SET ${tableTd} WHERE Id = ${edit_id} and type_id=${type_id}`,
                            sqlArr: tableVal
                        }, {
                            sql: `UPDATE classify_three SET classify_one_id = ? WHERE classify_two_Id = ${edit_id} and type_id=${type_id}`,
                            sqlArr: [classify_one_id]
                        }];
                        db.sqlConnectTransaction(transaction, (err) => {
                            if (err) {
                                utils.error(res, "修改分类失败");
                                return;
                            }
                            utils.success(res, "修改分类成功")
                        })
                        break;
                    default:
                        const sql = `UPDATE ${tableName} SET ${tableTd} WHERE Id = ${edit_id} and type_id=${type_id}`;
                        const sqlArr = tableVal;
                        const callBack = (err, result) => {
                            if (err) {
                                utils.error(res, "修改分类失败");
                                return;
                            }
                            utils.success(res, "修改分类成功")
                        }
                        db.sqlConnect(sql, sqlArr, callBack);
                        break;
                }
            }
            db.sqlConnect(sql, sqlArr, callBack);
        }
        switch (rank) {
            case 1:
                if (edit_id) {//edit_id 为null是添加分类否则是修改分类
                    const tableTd = 'name=?';
                    const tableVal = [name];
                    pluginClassify('classify_one', tableTd, tableVal);
                }
                else {
                    const tableTd = 'name,type_id,create_time';
                    const tableVal = [name, type_id, create_time];
                    pluginClassify('classify_one', tableTd, tableVal);
                }
                break;
            case 2:
                if (edit_id) {//edit_id 为null是添加分类否则是修改分类
                    const tableTd = 'name=?,classify_one_id=?';
                    const tableVal = [name, classify_one_id];
                    pluginClassify('classify_two', tableTd, tableVal);
                }
                else {
                    const tableTd = 'name,type_id,classify_one_id,create_time';
                    const tableVal = [name, type_id, classify_one_id, create_time];
                    pluginClassify('classify_two', tableTd, tableVal);
                }
                break;
            case 3:
                if (edit_id) {//edit_id 为null是添加分类否则是修改分类
                    const tableTd = 'name=?,classify_one_id=?,classify_two_id=?';
                    const tableVal = [name, classify_one_id, classify_two_id];
                    pluginClassify('classify_three', tableTd, tableVal);
                }
                else {
                    const classify_one_id = data.classify_one_id;
                    const classify_two_id = data.classify_two_id;
                    const tableTd = 'name,type_id,classify_one_id,classify_two_id,create_time';
                    const tableVal = [name, type_id, classify_one_id, classify_two_id, create_time];
                    pluginClassify('classify_three', tableTd, tableVal);
                }
                break;
        }
        return;
    },

    /**添加三级分类详细 */
    addDetail (req, res) {
        const data = req.body;
        const id = data.id;
        const summarize = data.summarize;//概述
        const argumentsArray = JSON.stringify(data.argumentsArray);//参数
        const demo = JSON.stringify(data.demo);//示例
        const sql = `UPDATE classify_three SET summarize = ?,argumentsArray = ?,demo = ? WHERE id = ${id}`;
        const sqlArr = [summarize, argumentsArray, demo];
        const callBack = (err, result) => {
            if (err) {
                utils.error(res, "添加详细失败");
                return;
            }
            utils.success(res, "添加成功")
        }
        db.sqlConnect(sql, sqlArr, callBack)
    },

    /**删除分类 */
    delClassify (req, res) {
        const params = req.query;
        const id = Number(params.id);
        const rank = Number(params.rank);
        let sql = null;
        const sqlArr = [];
        const callBack = (err, result) => {
            if (err) {
                utils.error(res, "删除失败");
                return;
            }
            utils.success(res, "删除成功")
        }
        switch (rank) {
            case 1:
                sql = [{
                    sql: `DELETE FROM classify_one WHERE id = ${id}`,
                    sqlArr: []
                }, {
                    sql: `DELETE FROM classify_two WHERE classify_one_id =  ${id}`,
                    sqlArr: []
                }, {
                    sql: `DELETE FROM classify_three WHERE classify_one_id =  ${id}`,
                    sqlArr: []
                }];
                break;
            case 2:
                sql = [{
                    sql: `DELETE FROM classify_two WHERE id =  ${id}`,
                    sqlArr: []
                }, {
                    sql: `DELETE FROM classify_three WHERE classify_two_id =  ${id}`,
                    sqlArr: []
                }];
                break;
            case 3:
                sql = [{
                    sql: `DELETE FROM classify_three WHERE id = ${id}`,
                    sqlArr: []
                }];
                break;
        }
        db.sqlConnectTransaction(sql, (err) => {
            if (err) {
                utils.error(res, "删除失败");
                return;
            }
            utils.success(res, "删除成功")
        });
    },

    /**修改分类状态 */
    stateClassify (req, res) {
        const params = req.query;
        const rank = Number(params.rank);
        const state = params.state == 'true' ? 1 : 0;
        const id = params.id;
        let tableName = null;
        switch (rank) {
            case 1:
                tableName = 'classify_one';
                break;

            case 2:
                tableName = 'classify_two';
                break;
            case 3:
                tableName = 'classify_three';
                break;
        }
        const sql = `UPDATE ${tableName} SET state=${state} WHERE id=${id}`;
        const sqlArr = [];
        const callBack = (err, result) => {
            if (err) {
                utils.error(res, "修改状态失败");
                return;
            }
            utils.success(res, "修改成功")
        }
        db.sqlConnect(sql, sqlArr, callBack)

    },

    /**查询一级分类下拉后的二级分类*/
    queryClassify (req, res) {
        const params = req.query;
        const rank = params.rank;//添加几级分类
        const type_id = params.type_id;
        const classify_one_id = params.classify_one_id;
        let sql = null;
        if (rank == 3) {
            sql = `SELECT * FROM classify_two WHERE type_id=${type_id} AND state=1 AND classify_one_id=${classify_one_id} `;
        }
        else {
            sql = `SELECT * FROM classify_one WHERE type_id=${type_id} AND state=1`;
        }
        const sqlArr = [];
        const callBack = (err, result) => {
            if (err) {
                utils.error(res, "查询分类失败");
                return;
            }
            utils.success(res, '查询分类成功', result)
        }
        db.sqlConnect(sql, sqlArr, callBack)
    },

    /**查询分类列表 */
    queryList (req, res) {
        const params = req.query;
        const rank = Number(params.rank);//查询第几级分类列表
        const type_id = params.type_id;
        const searchData = JSON.parse(params.searchData);//模糊查询
        let sql = null;
        switch (rank) {
            case 1:
                sql = `SELECT id, name, COUNT( twoClassifyNum ) classify_two_num,  COUNT( threeClassifyNum ) classify_three_num, state,updated
                FROM(
                    SELECT a.id, a.name, b.classify_one_id twoClassifyNum, NULL threeClassifyNum, a.state, a.update_time updated
                        FROM classify_one a LEFT JOIN classify_two b ON a.id = b.classify_one_id 
                        WHERE a.type_id=${type_id}
                    UNION ALL
                    SELECT a.id, a.name, NULL twoClassifyNum, b.classify_one_id threeClassifyNum, a.state,a.update_time updated 
                        FROM classify_one a LEFT JOIN classify_three b ON a.id = b.classify_one_id 
                        WHERE a.type_id=${type_id}
                ) aa 
                WHERE aa.name LIKE '%${searchData.name}%'  
                GROUP BY aa.id`
                break;

            case 2:
                sql = `SELECT id, id2, name, name2, COUNT( threeClassifyNum ) classify_three_num, state, updated 
                    FROM(
                        SELECT a.id id2, b.id id, a.name name2, b.name name, null threeClassifyNum, a.state, a.update_time updated 
                            FROM classify_two a LEFT JOIN classify_one b ON a.classify_one_id = b.id 
                            WHERE a.type_id=${type_id}
                        UNION ALL
                        SELECT a.id id2, null id, a.name name2, null name, b.classify_two_id threeClassifyNum, a.state, a.update_time updated 
                            FROM classify_two a LEFT JOIN classify_three b ON a.id = b.classify_two_id 
                            WHERE a.type_id=${type_id}
                    ) aa 
                    WHERE aa.name2 LIKE '%${searchData.name}%'  
                    GROUP BY aa.id2`
                break;
            case 3:
                sql = `SELECT id, name, SUM(name2) name2, name3, classify_one_id, classify_two_id, state, updated, summarize,argumentsArray,demo
                     FROM (
                        SELECT a.id id, b.NAME name, NULL name2, a.NAME name3, a.classify_one_id classify_one_id, a.classify_two_id classify_two_id, a.state, a.update_time updated, a.summarize, a.argumentsArray, a.demo 
                        FROM classify_three a LEFT JOIN classify_one b ON a.classify_one_id = b.id 
                        WHERE a.type_id=${type_id}
                        UNION ALL
                        SELECT a.id id, NULL name, b.name name2, a.NAME name3, a.classify_one_id classify_one_id, a.classify_two_id classify_two_id, a.state, a.update_time updated, a.summarize, a.argumentsArray, a.demo
                        FROM classify_three a LEFT JOIN classify_two b ON b.id = a.classify_two_id
                        WHERE a.type_id=${type_id}
                        ) aa
                        WHERE aa.name3 LIKE '%${searchData.name}%'  
                        GROUP BY aa.id`
                break;
        }
        const sqlArr = [];
        const callBack = (err, result) => {
            if (err) {
                utils.error(res, "查询列表失败");
                return;
            }
            utils.success(res, "查询列表成", result)
        }
        db.sqlConnect(sql, sqlArr, callBack)
    }
}
module.exports = classify
